/*
 * EnigmaGM - A feature rich guild management system.
 * Copyright (C) 2007 ajiaojr.
 * 
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
 * 02110-1301, USA.
 */
package org.ajiaojr.enigmagm.db.mysql;

import org.ajiaojr.enigmagm.client.data.Item;
import org.ajiaojr.enigmagm.client.service.DataAccessException;
import org.ajiaojr.enigmagm.db.ConnectionManager;
import org.ajiaojr.enigmagm.db.ItemDAO;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class MySQLItemDAOImpl implements ItemDAO {
  protected static final Log LOG =
      LogFactory.getLog(MySQLItemDAOImpl.class);

  public MySQLItemDAOImpl() {
  }

  public Item findByItemID(int itemID) throws DataAccessException {
    Item result = null;

    Connection c = null;

    try {
      c = ConnectionManager.getInstance().getConnection();

      Statement s = c.createStatement();

      String sql = "SELECT item_name FROM item WHERE item_id=" + itemID;

      if (LOG.isDebugEnabled()) {
        LOG.debug("finding items according to item ID using query: " + sql);
      }

      ResultSet rs = s.executeQuery(sql);

      if (rs.next()) {
        result = new Item(itemID, rs.getString(1), null, 0, 0);
      }

      rs.close();
      s.close();
    } catch (Exception e) {
      LOG.error("Error: ", e);
      throw new DataAccessException(e.getMessage());
    } finally {
      try {
        c.close();
      } catch (SQLException e) {
        LOG.error("Error: ", e);
      }
    }
    return result;
  }

  public List findByPointSystemId(int pointSystemId, String sortAttribute,
      boolean ascending, int firstResult, int maxResults)
      throws DataAccessException {
    Connection c = null;

    try {
      c = ConnectionManager.getInstance().getConnection();

      List result = new ArrayList();

      Statement s = c.createStatement();

      String sql =
          "SELECT item_id, item_name, item_quality_name, value FROM item_value NATURAL JOIN item NATURAL JOIN item_quality WHERE item_value.point_system_id = "
              + pointSystemId
              + " ORDER BY "
              + sortAttribute
              + " "
              + (ascending ? "ASC" : "DESC")
              + " LIMIT "
              + firstResult
              + ", "
              + maxResults;

      if (LOG.isDebugEnabled()) {
        LOG.debug("finding items according to point system id using query: "
            + sql);
      }

      ResultSet r = s.executeQuery(sql);

      while (r.next()) {
        result.add(new Item(r.getInt(1), r.getString(2), r.getString(3),
            pointSystemId, r.getDouble(4)));
      }

      r.close();
      s.close();

      return result;
    } catch (Exception e) {
      LOG.error("Error: ", e);
      throw new DataAccessException(e.getMessage());
    } finally {
      try {
        c.close();
      } catch (SQLException e) {
        LOG.error("Error: ", e);
      }
    }
  }

}
